package com.emm.yixun.website.utils;

import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.emm.yixun.website.task.utils.ExcelReader;

public class ExcelUtil {

	/**
	 * 导出到excel
	 */
	public static void exportExcel(List<List<Map<String, String>>> list, List<String[]> headerList, String[] name,HttpServletResponse response){
		try {
			  @SuppressWarnings("resource")
			  HSSFWorkbook wb = new HSSFWorkbook(); 
			  for(int n = 0; n < name.length; n++){
				  HSSFSheet sheet = wb.createSheet(); 
				  sheet.createFreezePane( 0, 1, 0, 1 );   
			      wb.setSheetName(n, name[n]);  
				  HSSFCellStyle headStyle = wb.createCellStyle(); 
				  HSSFFont f  = wb.createFont();      
				  f.setFontHeightInPoints((short) 15);// 字号   
			      f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗   
			      headStyle.setFont(f); 
			      headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
			      headStyle.setBorderBottom((short) 1);
			      headStyle.setBorderRight((short) 1);
			      headStyle.setFillBackgroundColor((short) 20);
			      HSSFRow row = sheet.createRow(0); 
			      //创建单元格，并设置值表头 设置表头居中  
			      HSSFCellStyle style = wb.createCellStyle();  
			      style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
			      style.setBorderBottom((short) 1);
			      style.setBorderRight((short) 1);
			      
			      HSSFCell cell = null;
			      String[] header = headerList.get(n);
			      for (int i = 0; i < header.length; i++) { 
			    	  	sheet.setColumnWidth(i, header[i].getBytes().length*2*256);
			            cell = row.createCell(i);  
			            cell.setCellStyle(style);  
			            HSSFRichTextString text = new HSSFRichTextString(header[i]);  
			            cell.setCellValue(text);  
			      }  

			      List<Map<String, String>> mapList = list.get(n);
		    	  for (int j = 0; j < mapList.size(); j++) {
		    		  Map<String, String> map = mapList.get(j);
		    		  row = sheet.createRow((int) j + 1);  
		    		  for (int i = 0; i < header.length; i++) {
			    		  cell = row.createCell(i);
			              cell.setCellValue(map.get(header[i]+"_"+j) == null ? "" : map.get(header[i]+"_"+j));
		    		  }
				 }
			 }
			 SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
			 String downFileName = name[0]+df.format(new Date()) + ".xls";
			 downFileName = URLEncoder.encode(downFileName, "UTF-8"); 
			 response.reset();
			 response.setCharacterEncoding("utf-8");
			 response.setHeader("Content-Disposition","attachment;filename="+downFileName);
			 response.setContentType("application/vnd.ms-excel;charset=utf-8");  
			 OutputStream out = response.getOutputStream();
			 wb.write(out);
			 out.close();
			 out.flush();  
			 out.close();  
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 导入excel
	 */
	public static List<Map<String, String>> importExcel(InputStream file, String fileType){
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		try {
			Workbook wb = null;
			if (fileType.equals("xls")) {
				wb = new HSSFWorkbook(file);
			} else if (fileType.equals("xlsx")) {
				wb = new XSSFWorkbook(file);
			} 
			Sheet sheet1 = wb.getSheetAt(0);
			for (int i = 1; i <= sheet1.getLastRowNum(); i++) {
				Row row = sheet1.getRow(i);
				
				Map<String, String> map = new HashMap<String, String>();
				Integer totalCell = (int) row.getLastCellNum(); //获取excel列数
				for (int j = 0; j < totalCell; j++) { 
					String cell = ExcelReader.getCell(row.getCell(j));
					map.put("excel_"+j, cell);
				}
				list.add(map);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return list;
	}
}
